<?php
 if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mcontent extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function Search($keyword,$limit,$stt)
    {
		$sql_limit = 'LIMIT '.$stt.','.$limit;
		$data1 =  $this->db->query("SELECT id,title AS name,img_thumbnail AS image FROM ecoupon WHERE title_ascii LIKE '%".$keyword."%' ".$sql_limit)->result_object();
		
        $data2 =  $this->db->query("SELECT id,name,logo AS image FROM restaurant WHERE name_ascii LIKE '%".$keyword."%' ".$sql_limit)->result_object();$data2->type = 2;
        $data3 =  $this->db->query("SELECT id,title AS name, image FROM incentive_restaurant WHERE title_ascii LIKE '%".$keyword."%' ".$sql_limit)->result_object();$data3->type = 3;
        $data4 =  $this->db->query("SELECT restaurant_id AS id,content AS name,'' AS image FROM restaurant_review WHERE improved = 1 AND content LIKE '%".$keyword."%' ".$sql_limit)->result_object();$data4->type = 4;
        $data5 =  $this->db->query("SELECT coupon_id AS id,content AS name,'' AS image FROM ecoupon_review WHERE improved = 1 AND content LIKE '%".$keyword."%' ".$sql_limit)->result_object();$data5->type = 5;
		foreach ( $data1 AS $el1=>$le1 ){
			$data1[$el1]->type = 1;
		}
		foreach ( $data2 AS $el2=>$le2 ){
			$data2[$el2]->type = 2;
		}
		foreach ( $data3 AS $el3=>$le3 ){
			$data3[$el3]->type = 3;
		}
		foreach ( $data4 AS $el4=>$le4 ){
			$data4[$el4]->type = 4;
		}
		foreach ( $data5 AS $el5=>$le5 ){
			$data5[$el5]->type = 5;
		}
		$data_ = array_merge($data1,$data2,$data3,$data4,$data5);
		return (object)$data_;
    }
    
    public function getNewIncentive($limit,$stt,$province_id,$is_partner){
        if(in_array($is_partner,array(null,'','0',0))){
            if(in_array($province_id, array(null,'','0',0))){ //các tỉnh khác ngoại trừ hn và hcm
                $data = $this->db->query("SELECT ir.id,ir.title,ir.title_ascii,ir.image,ir.slide_image AS post_image,ir.summary,ir.content,ir.type,ir.ecoupon,(CASE WHEN (ir.province_id = 1 AND (ipk.id_province != '79' OR ipk.id_province != '01')) THEN ipk.id_province ELSE 0 END) AS province_id,ir.is_partner,ir.created FROM incentive_restaurant AS ir LEFT JOIN incentive_province_key AS ipk ON ir.id = ipk.id_incentive WHERE (ir.province_id = 1 AND (ipk.id_province != '79' AND ipk.id_province != '01')) OR ir.province_id = 0 AND ir.active = 1 AND ir.is_partner = 0 ORDER BY ir.orderby ASC, ir.is_partner ASC LIMIT $stt,$limit ")->result_object();
            }else{ //các tỉnh theo id
                $data = $this->db->query("SELECT ir.id,ir.title,ir.title_ascii,ir.image,ir.slide_image AS post_image,ir.summary,ir.content,ir.type,ir.ecoupon,(CASE WHEN (ir.province_id = 1 AND ipk.id_province = '".$province_id."') THEN ipk.id_province ELSE 0 END) AS province_id,ir.is_partner,ir.created FROM incentive_restaurant AS ir LEFT JOIN incentive_province_key AS ipk ON ir.id = ipk.id_incentive WHERE (ir.province_id = 1 AND ipk.id_province = '".$province_id."') OR ir.province_id = 0 AND ir.active = 1 AND ir.is_partner = 0 ORDER BY ir.orderby ASC, ir.is_partner ASC LIMIT $stt,$limit ")->result_object();
            }
        }else if($is_partner === 1){ // đối tác
            $data = $this->db->query("SELECT id,title,title_ascii,image,slide_image AS post_image,summary,content,type,ecoupon,is_partner,created FROM incentive_restaurant WHERE active = 1 AND is_partner = 1 ORDER BY orderby ASC LIMIT $stt,$limit ")->result_object();
        }
        
		foreach($data AS $key => $value){
			$data[$key]->title = stripslashes($value->title);
			$data[$key]->content = stripslashes($value->content);
			$data[$key]->summary = stripslashes($value->summary);
		}
        
        return $data;
        //"SELECT ir.id,ir.title,ir.title_ascii,ir.image,ir.summary,ir.content,ir.type,ir.province_id,ir.created FROM incentive_restaurant AS ir LEFT JOIN incentive_restaurant_key AS irk ON irk.id_incentive = ir.id LEFT JOIN restaurant AS r ON r.id = irk.id_restaurant WHERE irk.id_restaurant = $label OR ir.type_sl = 0 AND ir.active = 1 ORDER BY created DESC LIMIT $stt,$limit "
    }
    
    
    public function getIncentiveFixed($limit,$stt){
        $data = $this->db->query("SELECT id,title,title_ascii,image,slide_image AS post_image,summary,content,ecoupon,type,province_id,created FROM incentive_restaurant WHERE type = 0 AND active = 1 ORDER BY orderby ASC LIMIT $stt,$limit ")->result_object();
		
		foreach($data AS $key => $value){
			$data[$key]->title = stripslashes($value->title);
			$data[$key]->content = stripslashes($value->content);
			$data[$key]->summary = stripslashes($value->summary);
		}
		
		return $data;
    }
    
    public function getIncentiveMonthly($limit,$stt){
        $data = $this->db->query("SELECT id,title,title_ascii,image,slide_image AS post_image,summary,content,ecoupon,type,province_id,created FROM incentive_restaurant WHERE type = 1 AND active = 1 ORDER BY orderby ASC LIMIT $stt,$limit ")->result_object();
		
		foreach($data AS $key => $value){
			$data[$key]->title = stripslashes($value->title);
			$data[$key]->content = stripslashes($value->content);
			$data[$key]->summary = stripslashes($value->summary);
		}
		
		return $data;
    }
    
    public function getRestaurantMenus($id){
        return $this->db->query("SELECT rm.id,rm.zone_id,rm.slide_image,z.title FROM restaurant_menus as rm JOIN zone as z ON rm.zone_id = z.id WHERE rm.restaurant_id = '".$id."' AND rm.active = 1 ORDER BY z.created DESC, rm.zone_id ASC ")->result_object();
    }
    
    
    public function getReviewRestaurant($id,$limit,$stt){
		// fix convert datetime
        return $this->db->query("SELECT id, restaurant_id, user_id, `name`, star, content, improved, DATE_FORMAT(FROM_UNIXTIME(created), '%H:%i %d/%m/%Y') AS created FROM restaurant_review WHERE restaurant_id = ".$id." AND improved = 1 ORDER BY orderby ASC LIMIT $stt,$limit ")->result_object();
    }
    
	public function getReviewCoupon($id,$limit,$stt){
		return $this->db->query("SELECT *,DATE_FORMAT(FROM_UNIXTIME(created), '%H:%i %d/%m/%Y') AS created FROM ecoupon_review WHERE coupon_id = ".$id." AND improved = 1 ORDER BY created DESC LIMIT $stt,$limit ")->result_object();
	}
	
    
    public function submitReviewRestaurant($restaurant_id,$user_id,$name,$star,$content){
        $data = array (
            'restaurant_id' => $restaurant_id,
            'user_id' => $user_id,
            'name' => $name,
            'star' => $star,
            'content' => $content,
            'created' => time(),
            'improved' => 0
            );
            
        $run = $this->db->insert('restaurant_review', $data );
        if($run){
            return 200;
        }else{
            return 201;
        }
    }
    
    
    
     public function submitReviewCoupon($coupon_id,$user_id,$name,$star,$content){
        $data = array (
            'coupon_id' => $coupon_id,
            'user_id' => $user_id,
            'name' => $name,
            'star' => $star,
            'content' => $content,
            'created' => time(),
            'improved' => 0
            );
            
        $run = $this->db->insert('ecoupon_review', $data );
        if($run){
            return 200;
        }else{
            return 201;
        }
    }
    
	public function submitBooking($user_id,$name,$phone,$email,$dates,$time,$restaurant_id,$address_id,$peoples = 1,$is_gpeople,$required){
		$data = array (
				'name' => $name,
				'phone' => $phone,
				'email' => $email,
				'time_booking' => $time.'|'.$dates,
				'address_id' => $address_id,
				'peoples' => $peoples,
				'is_gpeople' => $is_gpeople,
				'required' => $required,
				'improved' => 0,
				'restaurant_id' => $restaurant_id,
				'user_id' => $user_id,
				'created' => time()		
		);
		$run = $this->db->insert('booking', $data);
		if( $run ){
			$emailAdmin =  $this->getEmailAdmin(); //email admin
			//echo '<pre>';die(print_r($emailAdmin));
			$emailCustomer = $email;
			$data_add = $this->getAddressName($data['address_id']);  // data address
			$this->sendMailAdmin($emailAdmin,$data,$data_add);
			$this->sendMailMember($emailCustomer,$data,$data_add);
			return 200;
		}else{
			return 201;
		}
		
	}
	
	public function sendMailAdmin($emailAdmin,$data,$data_add){
		$config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email',$config);
		foreach ( $emailAdmin AS $el=>$le ){
			$date_booking = explode('|',$data['time_booking']);
			$date_book = $date_booking[0].' - Ngày : '.$date_booking[1];
		
				$body  = "<img src='http://125.212.192.114/ggift/data/images/other/mail-header-gg.jpg' style='width: 825px;'/><br/><br/><strong> Chào ";
			$body .= $le->fullname ? $le->fullname : 'Bạn';
			$body .= "!</strong> <br /><br /> Có 1 khách hàng vừa đăng kí đặt bàn tại nhà hàng của bạn !<br /><b>Thông tin đặt bàn:</b><br/>
			Họ tên : ".$data['name']."<br/>
			Số điện thoại : ".$data['phone']."<br/>
			Địa chỉ email :  ".$data['email']."<br/>
			Thời gian đặt bàn : ".$date_book."<br/>
			Số người : ".$data['peoples']."<br/>
			Nhà hàng : ".$data_add->name_res."<br/>
			Địa chỉ : ".$data_add->res_add."<br/>
			<br/><br/><img src='http://125.212.192.114/ggift/data/images/other/mail-footer-gg.jpg' style='width: 825px;'/>";
				
				$this->email->from($this->_smtp_user, 'G-People');
				$this->email->to((string)$le->email);
                if($el == 0){
                    $this->email->cc('callcenter@blueriver.vn,mkt.parttime@ggg.com.vn');
                }
				$this->email->subject('Thông tin đặt bàn tại '.$data_add->name_res);
				$this->email->message($body);    
				
				if($this->email->send()){
				    $this->email->clear(TRUE);
				}
		}
		$date_booking = explode('|',$data['time_booking']);
			$date_book = $date_booking[0].' - Ngày : '.$date_booking[1];
		
				$body  = "<img src='http://125.212.192.114/ggift/data/images/other/mail-header-gg.jpg' style='width: 825px;'/><br/><br/><strong> Chào ";
			$body .= 'Thu Trang';
			$body .= "!</strong> <br /><br /> Có 1 khách hàng vừa đăng kí đặt bàn tại nhà hàng của bạn !<br /><b>Thông tin đặt bàn:</b><br/>
			Họ tên : ".$data['name']."<br/>
			Số điện thoại : ".$data['phone']."<br/>
			Địa chỉ email :  ".$data['email']."<br/>
			Thời gian đặt bàn : ".$date_book."<br/>
			Số người : ".$data['peoples']."<br/>
			Nhà hàng : ".$data_add->name_res."<br/>
			Địa chỉ : ".$data_add->res_add."<br/><br/><br/><img src='http://125.212.192.114/ggift/data/images/other/mail-footer-gg.jpg' style='width: 825px;'/>
			";
				
				$this->email->from($this->_smtp_user, 'G-People');
				$this->email->to('thutrang.tran@ggg.com.vn');
				$this->email->subject('Thông tin đặt bàn tại '.$data_add->name_res);
				$this->email->message($body);    
				
				if($this->email->send()){
				    $this->email->clear(TRUE);
				}
	}
	
	public function sendMailMember($emailMember,$data,$data_add){
           $config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email',$config);
			$date_booking = explode('|',$data['time_booking']);
			$date_book = $date_booking[0].' - Ngày : '.$date_booking[1];
			$body = "<img src='http://125.212.192.114/ggift/data/images/other/mail-header-gg.jpg' style='width: 825px;'/><br/><br/>Chúc mừng Quý khách đã đăng ký đặt bàn thành công tại <b>".$data_add->name_res."</b>! Nhà hàng sẽ liên hệ với Quý khách qua điện thoại trong vòng 24h để xác nhận thông tin. <br /><br />Để không bỏ lỡ bất kỳ ưu đãi hấp dẫn từ hệ thống nhà hàng của Golden Gate Restaurant Group, Quý khách có thể thực hiện theo một trong những cách sau đây: <br /><br />- <a target='_blank' href='https://www.facebook.com/GoldenGateRestaurantGroup'>Like fanpage Golden Gate Restaurant Group</a> để cập nhật ưu đãi hàng ngày <br />- <a target='_blank' href='http://ggg.com.vn/promotions/data/dang-ky-nhan-thong-tin/'>Đăng ký</a> để nhận thông tin ưu đãi qua Email và SMS Để góp ý cho hệ thống của chúng tôi, vui lòng gửi ý kiến tới địa chỉ email <b>hn.support@ggg.com.vn</b>.<br /><br /> Golden Gate Restaurant Group mong luôn nhận được sự ủng hộ của Quý khách!<br/><br/><img src='http://125.212.192.114/ggift/data/images/other/mail-footer-gg.jpg' style='width: 825px;'/>";

			$this->email->from($this->_smtp_user, 'G-People');
				$this->email->to((string)$emailMember);    
				$this->email->subject('Thông tin đặt bàn tại '.$data_add->name_res);
				$this->email->message($body);    
				if($this->email->send()){
				    $this->email->clear(TRUE);
				}
	}
	
	public function getEmailAdmin(){   // [{email: "dungdcth@gmail.com",fullname: "mod"},{email: "bocdau@gmail.com",fullname: "Nguyễn Văn Nhất"}];
		return $this->db->query('SELECT u.email as email, u.fullname FROM user as u JOIN permission as p ON u.id = p.id_user WHERE p.id_module = 16 ')->result_object();
	}
	public function getAddressName($address_id){ //{name_res: "Nhà hàng bia hải xồm",res_add: "335 Phố Huê, Hai Bà Tưng"}
		return $this->db->query('SELECT r.name as name_res, ra.address as res_add FROM restaurant as r JOIN restaurant_address as ra ON r.id = ra.restaurant_id WHERE ra.id = '.$address_id)->row_object();
	}
	
    
    public function loginUser()
    {
        
    }
    
    
    public function registerUser($username)
    {
        $pass = $this->generateCode(6);
        $pass_encode = md5(md5($username).md5($pass));
        $qr = $this->db->query('SELECT * FROM user WHERE username = "'.strtolower($username).'"');
        $check = $qr->num_rows();
        
        if($check == 0)
        {
            $data = array(
                'username'  => $username,
                'password'  => $pass_encode
            );  
            $this->db->insert('user', $data);
	        $ID = $this->db->insert_id();
            $this->sendOTP($username, $pass);
            
            $_data = $this->db->query('SELECT * FROM user WHERE id = '.$ID)->row_object();
            $_data->password = null;
            return (object)array('status' => 200, 'content' => $_data);
        }else{
            $data = array(
                'password'  => $pass_encode
            );  
            $this->db->update('user', $data, "username = '".strtolower($username)."'");
            $this->sendOTP($username, $pass);
            $_data = $this->db->query('SELECT * FROM user WHERE username = "'.strtolower($username).'"')->row_object();
            $_data->password = null;
            return (object)array('status' => 200, 'content' => $_data);
        }   
    }
    
    public function getDataUser($id)
    {
        return $this->db->query('SELECT * FROM user WHERE id = '.$id)->row_object();
    }
    
    public function infoUser($id)
    {
        return $this->db->query('SELECT * FROM user WHERE id = '.$id)->row_object();
    }
    
    
    private function exNumberPhone($num)
    {
        return '84'.substr($num, 1);
    }
    
    private function sendOTP($dest, $otp, $types = 0)
    {
        if($types == 1)
        {
            $body = "VTV New Password:\n Mat khau moi cua ban la: ".$otp;
        }else{
            $body = "VTV OTP Code:\n Ma xac thuc cua ban la: ".$otp;
        }
        $NumberPhone = $this->exNumberPhone($dest);
    	$moid = '0';
    	$moseq = '0';
    	$src = '8069';
    	$cmdcode = '';
    	$msgtype = 'Text';
    	$msgtitle = '';
    	$mttotalseg = '1';
    	$mtseqref = '1';
    	$procresult ='1';
	$cpid = '10424';
    	$username = '3';
    	$password = 'e#@$';
    	$WSDL = "http://agent.gapit.com.vn/AgentWS_SureM/SMSAgentWS.asmx?wsdl";
    	$client = new SoapClient($WSDL);
    	$mtseq = date('YmdHis').rand(0,999);
    
    	$data = array(
    		'mtseq'         =>    $mtseq,
    		'moid'          =>    $moid,
    		'moseq'         =>    $moseq,
    		'src'           =>    $src,
    		'dest'          =>    $NumberPhone,
    		'cmdcode'       =>    $cmdcode,
    		'msgbody'       =>    $body,
    		'msgtype'       =>    $msgtype,
    		'msgtitle'      =>    $msgtitle,
    		'mttotalseg'    =>    $mttotalseg,
    		'mtseqref'      =>    $mtseqref,
    		'cpid'          =>    $cpid,
    		'reqtime'       =>    date("d/m/Y h:i:s A"),
    		'serviceid'     =>    'Service01',
    		'procresult'    =>    $procresult,        
    		'username'      =>    $username,
    		'password'      =>    $password
    	);
    
    	$status = $client->SendMT($data);         
    }
    
    
    public function changePassDB()
    {
        $uname = $this->getParamString('username');
        $upassold = $this->getParamString('passwordold');
        $upassnew = $this->getParamString('passwordnew');
        $passwordcfr = $this->getParamString('passwordcfr');

        $pass_encode = md5(md5($uname).md5($upassold));
        
        $sql = $this->db->query("SELECT * FROM user WHERE username = '".strtolower($uname)."' AND password = '".$pass_encode."' ");
        if($sql->num_rows() > 0)
        {
            if($upassnew != $passwordcfr)
            {
                return 201;
            }
    
            $pass_en_code = md5(md5($uname).md5($upassnew));
            $data = array(
                'password'  => $pass_en_code
            );  
            $this->db->update('user', $data, "username = '".strtolower($uname)."'");
            return 200;
        }else{
            return 404;
        }

    }
    
    
    public function forGotDB()
    {
        $pass = $this->generateCode(6);
        $uname = $this->getParamString('username');
        $pass_encode = md5(md5($uname).md5($pass));
        
        $qr = $this->db->query('SELECT * FROM user WHERE username = "'.strtolower($uname).'"');
        $check = $qr->num_rows();
        
        if($check > 0)
        {
            $data = array(
                'password'  => $pass_encode
            );  
            $this->db->update('user', $data, "username = '".strtolower($uname)."'");
            $this->sendOTP($uname, $pass, 1);
            return 200;
        }else{
            return 404;
        }
    }
    
    
    public function followDB()
    {
        $id_user = $this->getParamInt('user_id');
        $id_tvshow = $this->getParamInt('id_tvshow');
        $sql = $this->db->query('SELECT id FROM follow_tvshow WHERE user_id = '.$id_user.' AND tvshow_id = '.$id_tvshow);
        
        $check = $sql->num_rows();
        if($check > 0)
        {
            $id = $sql->row_object()->id;
            $this->db->delete('follow_tvshow', "id = ".$id);
            return (object)array('status' => 201, 'Hủy thành công');
        }else{
            $this->db->insert('follow_tvshow', array('user_id' => $id_user, 'tvshow_id' => $id_tvshow, 'created' => time()));
            return (object)array('status' => 200, 'Thêm thành công');
        }
        return (object)array('status' => 202, 'Lỗi khác');
    }
    
	
    public function getIncentiveLabel($limit,$stt,$label){
        $data =  $this->db->query("SELECT ir.id,ir.title,ir.title_ascii,ir.image,ir.summary,ir.content,ir.type,ir.province_id,ir.created,ir.slide_image AS post_image FROM incentive_restaurant AS ir LEFT JOIN incentive_restaurant_key AS irk ON irk.id_incentive = ir.id LEFT JOIN restaurant AS r ON r.id = irk.id_restaurant WHERE irk.id_restaurant = $label OR ir.type_sl = 0 AND ir.active = 1 ORDER BY created DESC LIMIT $stt,$limit ")->result_object();
		foreach($data AS $key => $value){
			$data[$key]->title = stripslashes($value->title);
			$data[$key]->content = stripslashes($value->content);
			$data[$key]->summary = stripslashes($value->summary);
		}
        return $data;
    }
    
    public function getRatingLabel($label)
    {
        if(!in_array($label, array(null, ''))){
            $data_review = $this->db->query('SELECT rr.star AS star FROM restaurant AS r INNER JOIN restaurant_review AS rr ON rr.restaurant_id = r.id WHERE rr.improved = 1 AND r.id = '.$label)->result_object();
            $sum = 0; $dem = 0;
			foreach ( $data_review AS $al=>$la ){
				if ( !in_array($la->star,array('',null,0)) ){
					$sum += (int)$la->star;
					$dem++;
				}
			}
            return (object)array('rate' => number_format(($sum/$dem),'2', ',', '.'), 'review_count' => $dem);
        }else{
            $data = $this->db->query("SELECT id,name FROM restaurant")->result_object();
            foreach($data AS $key=>$val)
            {
                $data_review = $this->db->query('SELECT rr.star AS star FROM restaurant AS r INNER JOIN restaurant_review AS rr ON rr.restaurant_id = r.id WHERE rr.improved = 1 AND r.id = '.$val->id)->result_object();
                $sum = 0; $dem = 0;
    			foreach ( $data_review AS $al=>$la ){
    				if ( !in_array($la->star,array('',null,0)) ){
    					$sum += (int)$la->star;
    					$dem++;
    				}
    			}
                $rate = number_format(($sum/$dem),'2', ',', '.');
                $data[$key]->rate = $rate;
                $data[$key]->review_count = $dem;
            }
            return $data;
               
        }
    }
	
	public function countDownApp($device_name,$os_name){
		if( in_array($device_name,array('',null)) || in_array($os_name,array('',null)) ){
			return array('data' => '201', 'content' => 'Không thể thêm thiết bị mới');
		}else{
			$data = array(
				'device_name' => $device_name,
				'os_name' => $os_name,
				'created' => date('Y-m-d')
			);
		$this->db->insert('log_downapp', $data);
			return array('data' => '200', 'content' => 'Thêm thiết bị thành công');
		}
	}
	
}
